package util;

import java.sql.*;
import java.util.List;

import model.BacSi;
import model.Benh;
import model.ChuDe;
import model.ChuyenKhoa;
import model.DiaDiemYTe;
import model.LoaiBenh;
import model.LoaiDiaDiemYTe;
import model.LoaiThietBiYTe;
import model.LoaiThuoc;
import model.ThietBiYTe;
import model.ThongTinDB;
import model.Thuoc;

import org.hibernate.Session;

import dao.ChuDeDAO;
import dao.ChuyenKhoaDAO;
import dao.DAOFactory;
import dao.LoaiBenhDAO;
import dao.LoaiDiaDiemYTeDAO;
import dao.LoaiThietBiYTeDAO;
import dao.LoaiThuocDAO;
import dao.ThongTinDBDAO;
import dao.hibernate.HibernateDAOFactory;

public class SQLiteUtil {
	private static DAOFactory daoFactory = DAOFactory.instance(HibernateDAOFactory.class);
	private static ChuDeDAO chuDeDAO = daoFactory.getChuDeDAO();
	private static ChuyenKhoaDAO chuyenKhoaDAO = daoFactory.getChuyenKhoaDAO();
	private static LoaiBenhDAO loaiBenhDAO = daoFactory.getLoaiBenhDAO();
	private static LoaiThuocDAO loaiThuocDAO = daoFactory.getLoaiThuocDAO();
	private static LoaiDiaDiemYTeDAO loaiDiaDiemYTeDAO = daoFactory.getLoaiDiaDiemYTeDAO();
	private static LoaiThietBiYTeDAO loaiThietBiYTeDAO = daoFactory.getLoaiThietBiYTeDAO();
	private static ThongTinDBDAO thongTinDBDAO = daoFactory.getThongTinDBDAO();
	private static Session session = HibernateUtil.getCurrentSession();
	
	public static boolean createSQLiteDB() {
		try {
			Class.forName("org.sqlite.JDBC");
		    Connection conn = DriverManager.getConnection("jdbc:sqlite:" + Util.SQLITE_DB_FOLDER + Util.SQLITE_DB_FILENAME);
		    Statement stat = conn.createStatement();
		    //default table android_metadata
		    stat.executeUpdate("drop table if exists android_metadata;");
		    stat.executeUpdate("create table android_metadata (_id INT, locale VARCHAR);");
		    
		    //table 'benh'
		    createTableBenh(conn, stat);
		    
		    //table 'bac_si'
		    createTableBacSi(conn, stat);
		    
		    //table 'benh_bacsi'
		    createTableBenh_BacSi(conn, stat);
		    
		    //table 'benh_diadiemyte'
		    createTableBenh_DiaDiemYTe(conn, stat);
		    
		    //table 'benh_thuoc'
		    createTableBenh_Thuoc(conn, stat);
		    
		    //Android doesn't need this table
//		    //table 'chu_de'
//		    stat.executeUpdate("drop table if exists chu_de;");
//		    stat.executeUpdate("create table chu_de (idChuDe BIGINT PRIMARY KEY ASC, tenChuDe VARCHAR, idChuDeCha BIGINT, loai VARCHAR);");
		    
		    //table 'chuyen_khoa'
		    createTableChuyenKhoa(conn, stat);
		    
		    //table 'dia_diem_y_te'
		    createTableDiaDiemYTe(conn, stat);
		    
		    //table 'diadiemyte_chuyenkhoa'
		    createTableDiaDiemYTe_ChuyenKhoa(conn, stat);
		    
		    //table 'loai_benh'
		    createTableLoaiBenh(conn, stat);
		    
		    //table 'loai_thuoc'
		    createTableLoaiThuoc(conn, stat);
		    
		    //table 'loai_diadiemyte'
		    createTableLoaiDiaDiemYTe(conn, stat);
		    
		    //table 'loai_thietbiyte'
		    createTableLoaiThietBiYTe(conn, stat);
		    
		    //table 'thiet_bi_y_te'
		    createTableThietBiYTe(conn, stat);
		    
		    //table 'thuoc'
		    createTableThuoc(conn, stat);
		    
		    //table 'thong_tin_db'
		    createTableThongTinDB(conn, stat);
		    
		    conn.close();
		    System.out.println("SQLite Database created successfully !");
		    
		    return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
	}
	
	public static void createTableBenh(Connection conn, Statement stat) throws SQLException{
	    stat.executeUpdate("drop table if exists benh;");
	    stat.executeUpdate("create table benh (idBenh BIGINT PRIMARY KEY ASC, tenBenh VARCHAR, idLoaiBenh BIGINT, nguyenNhan TEXT, trieuChung TEXT, chanDoan TEXT, cachChuaTriTayY TEXT, cachChuaTriDongY TEXT, phongChongBenh TEXT);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(Benh.class);
	    Benh benh = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into benh values (?, ?, ?, ?, ?, ?, ?, ?, ?);");
	    for (ChuDe chuDe : listChuDe) {
			benh = (Benh)chuDe;
			prep.setLong(1, benh.getIdChuDe());
		    prep.setString(2, benh.getTenChuDe());
		    prep.setLong(3, benh.getLoaiBenh().getIdLoaiBenh());
		    prep.setString(4, benh.getNguyenNhan());
		    prep.setString(5, benh.getTrieuChung());
		    prep.setString(6, benh.getChanDoan());
		    prep.setString(7, benh.getCachChuaTriTayY());
		    prep.setString(8, benh.getCachChuaTriDongY());
		    prep.setString(9, benh.getPhongChongBenh());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableBacSi(Connection conn, Statement stat) throws SQLException{
	    stat.executeUpdate("drop table if exists bac_si;");
	    stat.executeUpdate("create table bac_si (idBacSi BIGINT PRIMARY KEY ASC, tenBacSi VARCHAR, idChuyenKhoa BIGINT, idNoiCongTac BIGINT, ngaySinh DATE, thamNien INT, diaChi VARCHAR, soDienThoai VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(BacSi.class);
	    BacSi bacSi = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into bac_si values (?, ?, ?, ?, ?, ?, ?, ?);");
	    for (ChuDe chuDe : listChuDe) {
			bacSi = (BacSi)chuDe;
			prep.setLong(1, bacSi.getIdChuDe());
		    prep.setString(2, bacSi.getTenChuDe());
		    prep.setLong(3, bacSi.getChuyenKhoa().getIdChuyenKhoa());
		    prep.setLong(4, bacSi.getNoiCongTac().getIdChuDe());
		    prep.setString(5, Util.getDateString(bacSi.getNgaySinh()));
		    prep.setInt(6, bacSi.getThamNien());
		    prep.setString(7, bacSi.getDiaChi());
		    prep.setString(8, bacSi.getSoDienThoai());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableBenh_BacSi(Connection conn, Statement stat) throws Exception{
		stat.executeUpdate("drop table if exists benh_bacsi;");
	    stat.executeUpdate("create table benh_bacsi (idBenh BIGINT, idBacSi BIGINT, PRIMARY KEY (idBenh, idBacSi));");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listBenh = chuDeDAO.findAllByChuDe(Benh.class);
	    Benh benh = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into benh_bacsi values (?, ?);");
	    for (ChuDe chuDe : listBenh) {
			benh = (Benh)chuDe;
			for (BacSi bacSi : benh.getDsBacSiChuyen()) {
				prep.setLong(1, benh.getIdChuDe());
				prep.setLong(2, bacSi.getIdChuDe());
				prep.addBatch();
			}
		}
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableBenh_DiaDiemYTe(Connection conn, Statement stat) throws Exception{
		stat.executeUpdate("drop table if exists benh_diadiemyte;");
	    stat.executeUpdate("create table benh_diadiemyte (idBenh BIGINT, idDiaDiemYTe BIGINT, PRIMARY KEY (idBenh, idDiaDiemYTe));");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listBenh = chuDeDAO.findAllByChuDe(Benh.class);
	    Benh benh = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into benh_diadiemyte values (?, ?);");
	    for (ChuDe chuDe : listBenh) {
			benh = (Benh)chuDe;
			for (DiaDiemYTe ddyt : benh.getDsDiaDiemYTeChuyen()) {
				prep.setLong(1, benh.getIdChuDe());
				prep.setLong(2, ddyt.getIdChuDe());
				prep.addBatch();
			}
		}
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableBenh_Thuoc(Connection conn, Statement stat) throws Exception{
		stat.executeUpdate("drop table if exists benh_thuoc;");
	    stat.executeUpdate("create table benh_thuoc (idBenh BIGINT, idThuoc BIGINT, PRIMARY KEY (idBenh, idThuoc));");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listBenh = chuDeDAO.findAllByChuDe(Benh.class);
	    Benh benh = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into benh_thuoc values (?, ?);");
	    for (ChuDe chuDe : listBenh) {
			benh = (Benh)chuDe;
			for (Thuoc thuoc : benh.getDsThuocChua()) {
				prep.setLong(1, benh.getIdChuDe());
				prep.setLong(2, thuoc.getIdChuDe());
				prep.addBatch();
			}
		}
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableChuyenKhoa(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists chuyen_khoa;");
	    stat.executeUpdate("create table chuyen_khoa (idChuyenKhoa BIGINT PRIMARY KEY ASC, tenChuyenKhoa VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuyenKhoa> listChuyenKhoa = chuyenKhoaDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into chuyen_khoa values (?, ?);");
	    for (ChuyenKhoa chuyenKhoa : listChuyenKhoa) {
			prep.setLong(1, chuyenKhoa.getIdChuyenKhoa());
		    prep.setString(2, chuyenKhoa.getTenChuyenKhoa());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableDiaDiemYTe(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists dia_diem_y_te;");
	    stat.executeUpdate("create table dia_diem_y_te (idDiaDiemYTe BIGINT PRIMARY KEY ASC, idLoaiDiaDiemYTe BIGINT, tenDiaDiemYTe VARCHAR, thongTin TEXT, diaChi VARCHAR, soDienThoai VARCHAR, viDo VARCHAR, kinhDo VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(DiaDiemYTe.class);
	    DiaDiemYTe ddyt = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into dia_diem_y_te values (?, ?, ?, ?, ?, ?, ?, ?);");
	    for (ChuDe chuDe : listChuDe) {
	    	ddyt = (DiaDiemYTe)chuDe;
			prep.setLong(1, ddyt.getIdChuDe());
		    prep.setLong(2, ddyt.getLoaiDiaDiemYTe().getIdLoaiDiaDiemYTe());
		    prep.setString(3, ddyt.getTenChuDe());
		    prep.setString(4, ddyt.getThongTin());
		    prep.setString(5, ddyt.getDiaChi());
		    prep.setString(6, ddyt.getSoDienThoai());
		    prep.setDouble(7, ddyt.getViDo());
		    prep.setDouble(8, ddyt.getKinhDo());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableDiaDiemYTe_ChuyenKhoa(Connection conn, Statement stat) throws Exception{
		stat.executeUpdate("drop table if exists diadiemyte_chuyenkhoa;");
	    stat.executeUpdate("create table diadiemyte_chuyenkhoa (idDiaDiemYTe BIGINT, idChuyenKhoa BIGINT, PRIMARY KEY (idDiaDiemYTe, idChuyenKhoa));");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(DiaDiemYTe.class);
	    DiaDiemYTe ddyt = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into diadiemyte_chuyenkhoa values (?, ?);");
	    for (ChuDe chuDe : listChuDe) {
			ddyt = (DiaDiemYTe)chuDe;
			for (ChuyenKhoa chuyenKhoa : ddyt.getDsChuyenKhoa()) {
				prep.setLong(1, ddyt.getIdChuDe());
				prep.setLong(2, chuyenKhoa.getIdChuyenKhoa());
				prep.addBatch();
			}
		}
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableLoaiBenh(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists loai_benh;");
	    stat.executeUpdate("create table loai_benh (idLoaiBenh BIGINT PRIMARY KEY ASC, tenLoaiBenh VARCHAR, idChuyenKhoa BIGINT);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<LoaiBenh> listLoaiBenh = loaiBenhDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into loai_benh values (?, ?, ?);");
	    for (LoaiBenh loaiBenh : listLoaiBenh) {
			prep.setLong(1, loaiBenh.getIdLoaiBenh());
		    prep.setString(2, loaiBenh.getTenLoaiBenh());
		    prep.setLong(3, loaiBenh.getChuyenKhoa().getIdChuyenKhoa());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableLoaiThuoc(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists loai_thuoc;");
	    stat.executeUpdate("create table loai_thuoc (idLoaiThuoc BIGINT PRIMARY KEY ASC, tenLoaiThuoc VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<LoaiThuoc> listLoaiThuoc = loaiThuocDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into loai_thuoc values (?, ?);");
	    for (LoaiThuoc loaiThuoc : listLoaiThuoc) {
			prep.setLong(1, loaiThuoc.getIdLoaiThuoc());
		    prep.setString(2, loaiThuoc.getTenLoaiThuoc());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableLoaiDiaDiemYTe(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists loai_diadiemyte;");
	    stat.executeUpdate("create table loai_diadiemyte (idLoaiDiaDiemYTe BIGINT PRIMARY KEY ASC, tenLoaiDiaDiemYTe VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<LoaiDiaDiemYTe> listLoaiDDYT = loaiDiaDiemYTeDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into loai_diadiemyte values (?, ?);");
	    for (LoaiDiaDiemYTe loaiDiaDiemYTe : listLoaiDDYT) {
			prep.setLong(1, loaiDiaDiemYTe.getIdLoaiDiaDiemYTe());
		    prep.setString(2, loaiDiaDiemYTe.getTenLoaiDiaDiemYTe());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableLoaiThietBiYTe(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists loai_thietbiyte;");
	    stat.executeUpdate("create table loai_thietbiyte (idLoaiThietBiYTe BIGINT PRIMARY KEY ASC, tenLoaiThietBiYTe VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<LoaiThietBiYTe> listLoaiThietBiYTe = loaiThietBiYTeDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into loai_thietbiyte values (?, ?);");
	    for (LoaiThietBiYTe loaiThietBiYTe : listLoaiThietBiYTe) {
			prep.setLong(1, loaiThietBiYTe.getIdLoaiThietBiYTe());
		    prep.setString(2, loaiThietBiYTe.getTenLoaiThietBiYTe());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableThietBiYTe(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists thiet_bi_y_te;");
	    stat.executeUpdate("create table thiet_bi_y_te (idThietBiYTe BIGINT PRIMARY KEY ASC, tenThietBiYTe VARCHAR, idLoaiThietBiYTe BIGINT, congDung TEXT, cachDung TEXT, nhaSanXuat VARCHAR, URLHinh VARCHAR);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(ThietBiYTe.class);
	    ThietBiYTe thietBi = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into thiet_bi_y_te values (?, ?, ?, ?, ?, ?, ?);");
	    for (ChuDe chuDe : listChuDe) {
	    	thietBi = (ThietBiYTe)chuDe;
			prep.setLong(1, thietBi.getIdChuDe());
		    prep.setString(2, thietBi.getTenChuDe());
		    prep.setLong(3, thietBi.getLoaiThietBiYTe().getIdLoaiThietBiYTe());
		    prep.setString(4, thietBi.getCongDung());
		    prep.setString(5, thietBi.getCachDung());
		    prep.setString(6, thietBi.getNhaSanXuat());
		    prep.setString(7, thietBi.getURLHinh());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableThuoc(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists thuoc;");
	    stat.executeUpdate("create table thuoc (idThuoc BIGINT PRIMARY KEY ASC, tenThuoc VARCHAR, idLoaiThuoc BIGINT, chiDinh TEXT, chongChiDinh TEXT, quyCachDongGoi VARCHAR, thanhPhan TEXT, nhaSanXuat VARCHAR, thamKhaoBacSi BOOLEAN);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ChuDe> listChuDe = chuDeDAO.findAllByChuDe(Thuoc.class);
	    Thuoc thuoc = null;
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into thuoc values (?, ?, ?, ?, ?, ?, ?, ?, ?);");
	    for (ChuDe chuDe : listChuDe) {
	    	thuoc = (Thuoc)chuDe;
			prep.setLong(1, thuoc.getIdChuDe());
		    prep.setString(2, thuoc.getTenChuDe());
		    prep.setLong(3, thuoc.getLoaiThuoc().getIdLoaiThuoc());
		    prep.setString(4, thuoc.getChiDinh());
		    prep.setString(5, thuoc.getChongChiDinh());
		    prep.setString(6, thuoc.getQuyCachDongGoi());
		    prep.setString(7, thuoc.getThanhPhan());
		    prep.setString(8, thuoc.getNhaSanXuat());
		    prep.setBoolean(9, thuoc.isThamKhaoBacSi());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void createTableThongTinDB(Connection conn, Statement stat) throws SQLException{
		stat.executeUpdate("drop table if exists thong_tin_db;");
	    stat.executeUpdate("create table thong_tin_db (id BIGINT PRIMARY KEY ASC, phienBan BIGINT, ngayCapNhat DATETIME, ghiChu TEXT);");
	    
	    session = HibernateUtil.getCurrentSession();
	    session.getTransaction().begin();
	    List<ThongTinDB> listThongTinDB = thongTinDBDAO.findAll();
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into thong_tin_db values (?, ?, ?, ?);");
	    for (ThongTinDB thongTinDB : listThongTinDB) {
			prep.setLong(1, thongTinDB.getId());
		    prep.setLong(2, thongTinDB.getPhienBan());
		    prep.setString(3, Util.getDateString(thongTinDB.getNgayCapNhat()));
		    prep.setString(4, thongTinDB.getGhiChu());
		    prep.addBatch();
		}
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    session.getTransaction().commit();
	}
	
	public static void demo() throws Exception{
		Class.forName("org.sqlite.JDBC");
	    Connection conn =
	      DriverManager.getConnection("jdbc:sqlite:db/sqlite/test.db");
	    Statement stat = conn.createStatement();
	    stat.executeUpdate("drop table if exists people;");
	    stat.executeUpdate("create table people (name, occupation);");
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into people values (?, ?);");

	    prep.setString(1, "Gandhi");
	    prep.setString(2, "politics");
	    prep.addBatch();
	    prep.setString(1, "Turing");
	    prep.setString(2, "computers");
	    prep.addBatch();
	    prep.setString(1, "Wittgenstein");
	    prep.setString(2, "smartypants");
	    prep.addBatch();

	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);

	    ResultSet rs = stat.executeQuery("select * from people;");
	    while (rs.next()) {
	      System.out.println("name = " + rs.getString("name"));
	      System.out.println("job = " + rs.getString("occupation"));
	    }
	    rs.close();
	    conn.close();
	}
	
	public static void main(String[] args) throws Exception {
//		createSQLiteDB();
	  }
}
